using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient; 
using System.Configuration; 

public class dbProcessor
{    
    public SqlConnection sq; 
        
    public dbProcessor()
        {
        
        System.Configuration.Configuration rootConfig;
        
        rootConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/Deimos");

        string connection_string = rootConfig.ConnectionStrings.ConnectionStrings["CompConn"].ConnectionString;
              
        sq = new SqlConnection(connection_string); 
     }

    #region COMPANY_DATA_MANIPULATION

   public void insert_entry(string Name, string Country, string City, string Address, string Phone, string Fax, string web_site, string email, string current_user, out int new_id)
    {
        SqlCommand sc=new SqlCommand();

        sc.CommandText = "INSERT INTO main_data (Name, Country, City, Address, Phone, Fax, email, web_site, Creator, created) VALUES (@Name, @Country, @City,  @Address, @Phone, @Fax, @email, @web_site, @Creator, CURRENT_TIMESTAMP)";

        sc.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar,56);
        sc.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@City", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@Address", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@Phone", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@Fax", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@email", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@web_site", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@Creator", System.Data.SqlDbType.Int); 
        
        sc.Parameters["@Name"].Value = Name;
        sc.Parameters["@Country"].Value = Country;
        sc.Parameters["@City"].Value = City; 
        sc.Parameters["@Address"].Value = Address;
        sc.Parameters["@Phone"].Value = Phone;
        sc.Parameters["@Fax"].Value = Fax;
        sc.Parameters["@email"].Value = email;
        sc.Parameters["@web_site"].Value = web_site;
        sc.Parameters["@Creator"].Value=get_user_id(current_user);
         
        sc.Connection = sq;
        sc.Connection.Open();
        sc.ExecuteNonQuery();

        sc.CommandText = "SELECT @@IDENTITY";
        new_id = Convert.ToInt32(sc.ExecuteScalar());                
        sc.Connection.Close();        
    }

   public void insert_activity_data(int CompId, String Description, String Keywords)
   {
       SqlCommand sc = new SqlCommand();

       sc.CommandText = "INSERT INTO activity_data (CompId, Description, Keywords) VALUES (@CompId, @Description, @Keywords)";
       sc.Parameters.Add("@CompId", System.Data.SqlDbType.Int);
       sc.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar);
       sc.Parameters.Add("@Keywords", System.Data.SqlDbType.NVarChar);

       sc.Parameters["@CompId"].Value = CompId;
       sc.Parameters["@Description"].Value = Description;
       sc.Parameters["@Keywords"].Value = Keywords;

       sc.Connection = sq;
       sc.Connection.Open();
       sc.ExecuteNonQuery();

       sc.Connection.Close();
   }

   public void update_entry(int ID, string Name, string Address, string email, string phone, string fax, string country, string city, string web)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "UPDATE main_data SET Name=@Name, Address=@Address, email=@email, phone=@phone, fax=@fax, country=@country, city=@city, web_site=@web  WHERE ID=@ID";

        sc.Parameters.Add("@ID", System.Data.SqlDbType.Int);
        sc.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@Address", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@email", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@country", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@city", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@phone", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@fax", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters.Add("@web", System.Data.SqlDbType.NVarChar, 56);

        sc.Parameters["@ID"].Value = ID;
        sc.Parameters["@Name"].Value = Name;
        sc.Parameters["@Address"].Value = Address;
        sc.Parameters["@email"].Value = email;
        sc.Parameters["@phone"].Value = phone;
        sc.Parameters["@fax"].Value = fax;
        sc.Parameters["@country"].Value = country;
        sc.Parameters["@city"].Value = city;
        sc.Parameters["@web"].Value = web;

        sc.Connection = sq;
        sc.Connection.Open();
        sc.ExecuteNonQuery();
        sc.Connection.Close();
    }

   public void select_entry(int ID, out string Name, out string Country, out string City, out string Address, out string Phone, out string Fax,  out string email, out string web_site, out String Description, out string LogoLink, out int Creator, out string created_date)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT m.Name, m.Country, m.City, m.Address, m.Phone, m.Fax, m.email, m.web_site, m.LogoLink, ad.Description, m.Creator, m.created FROM main_data as m LEFT JOIN activity_data as ad ON m.ID=ad.CompId WHERE m.ID=@ID";
        sc.Parameters.Add("@ID", System.Data.SqlDbType.Int);
        sc.Parameters["@ID"].Value = ID;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();

        Name = (string)dr["Name"];
        Country=(string)dr["Country"];
        City = (string)dr["City"];
        Address = (string)dr["Address"];
        Phone = (string)dr["Phone"];
        Fax = (string)dr["Fax"];
        email = (string)dr["email"];
        web_site = (string)dr["web_site"];

        if (!DBNull.Value.Equals(dr["LogoLink"]))
        {
            LogoLink="thumbs\\"+(string)dr["LogoLink"];
        }
        else
        {
            LogoLink = "thumbs\\default.jpeg";
        }
                               
        if(!DBNull.Value.Equals(dr["Description"]))
            {
                Description = (String)dr["Description"];
            }
        else
            {
                Description = ""; 
            }

        Creator = (int)dr["Creator"]; 
        
        DateTime dt=(DateTime)dr["created"];
        created_date = Convert.ToString(dt); 
        
        dr.Close();  
        sc.Connection.Close(); 
    }

   public void delete_entry(int ID)
   {
       SqlCommand sc = new SqlCommand();

       sc.CommandText = "DELETE FROM main_data WHERE ID=@ID";
       sc.Parameters.Add("@ID", System.Data.SqlDbType.Int);
       sc.Parameters["@ID"].Value = ID;
       
       sc.Connection = sq;
       sc.Connection.Open();
       sc.ExecuteNonQuery();
       sc.Connection.Close();
   }

   public void delete_activity_data(int ID)
   {
       SqlCommand sc = new SqlCommand();

       sc.CommandText = "DELETE FROM activity_data WHERE CompID=@ID";
       sc.Parameters.Add("@ID", System.Data.SqlDbType.Int);
       sc.Parameters["@ID"].Value = ID;
       sc.Connection = sq;
       sc.Connection.Open(); 
       sc.ExecuteNonQuery();
       sc.Connection.Close();
   }

   private int get_entries_count(int UserId)
   {
       SqlCommand sc = new SqlCommand();

       sc.CommandText = "SELECT count(*) FROM main_data WHERE Creator=@UserId";
       sc.Parameters.Add("@UserId", System.Data.SqlDbType.Int);
       sc.Parameters["@UserId"].Value = UserId;

       sc.Connection = sq;
       sc.Connection.Open();
         
       int recnum = (int)sc.ExecuteScalar();

       sc.Connection.Close(); 

       return recnum; 
   }

   public cmpEntry[] select_user_entries(int UserId)
    {
        cmpEntry[] cmpEntries = new cmpEntry[get_entries_count(UserId)];

        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT ID, Name, Address, email, LogoLink FROM main_data WHERE Creator=@UserId";
        sc.Parameters.Add("@UserId", System.Data.SqlDbType.Int);
        sc.Parameters["@UserId"].Value = UserId;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
              
        int cmpCount = 0;

        while (dr.Read())
        {
            cmpEntries[cmpCount] = new cmpEntry();

            cmpEntries[cmpCount].cmpID=(int)dr["ID"]; 
            cmpEntries[cmpCount].cmpName=(string)dr["Name"];
            cmpEntries[cmpCount].cmpAddress=(string)dr["Address"]; 
            cmpEntries[cmpCount].cmpEmail=(string)dr["email"];

            if (dr["LogoLink"] != System.DBNull.Value)
            {
                cmpEntries[cmpCount].cmpLogoLink = "thumbs\\"+(string)dr["LogoLink"];  
            }
            else
            {
                cmpEntries[cmpCount].cmpLogoLink = "thumbs\\default.jpeg";                  
            }
            
            cmpCount++;
        }

        dr.Close(); 
        sc.Connection.Close(); 

        return cmpEntries;
  }
    
   public int get_comp_id(string CompName, string CompAddress)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT ID FROM main_data WHERE Name=@Name AND Address=@Address";
        
        sc.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters.Add("@Address", System.Data.SqlDbType.NVarChar, 256);
        
        sc.Parameters["@Name"].Value = CompName;
        sc.Parameters["@Address"].Value = CompAddress;

        sc.Connection = sq;
        sc.Connection.Open(); 

        dr = sc.ExecuteReader();
        dr.Read(); 

        return (int)dr["ID"]; 
    }

   #endregion 

    #region USER_DATA_MANIPULATION

    public void post_user(string Login, string FirstName, string LastName, string email, string phone, string act_code)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "INSERT INTO user_data (Login, First_name, Last_name, email, phone, act_code) VALUES (@Login, @First_name, @Last_name, @email, @phone, @act_code)";

        sc.Parameters.Add("@Login", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters.Add("@First_name", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters.Add("@Last_name", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters.Add("@email", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters.Add("@phone", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters.Add("@act_code", System.Data.SqlDbType.NVarChar, 56);

        sc.Parameters["@Login"].Value = Login;
        sc.Parameters["@First_name"].Value = FirstName;
        sc.Parameters["@Last_name"].Value = LastName;
        sc.Parameters["@email"].Value = email;
        sc.Parameters["@phone"].Value = phone;
        sc.Parameters["@act_code"].Value = act_code;

        sc.Connection = sq;
        sc.Connection.Open();
        sc.ExecuteNonQuery();
        sc.Connection.Close();
    }

    public int get_user_id(string Login)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT ID FROM user_data WHERE Login=@Login";

        sc.Parameters.Add("@Login", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters["@Login"].Value = Login;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();
        
        int id = (int)dr["ID"];

        dr.Close(); 

        sc.Connection.Close();

        return id;
    }

    public string get_user_login(string act_code)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT Login FROM user_data WHERE act_code=@act_code";

        sc.Parameters.Add("@act_code", System.Data.SqlDbType.NVarChar, 56);
        sc.Parameters["@act_code"].Value = act_code;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();

        string Login = (string)dr["Login"];

        dr.Close();

        sc.Connection.Close();

        return Login;
    }

    public string get_full_name(int userId)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT ID, First_name, Last_name FROM user_data WHERE ID=@Id";

        sc.Parameters.Add("@Id", System.Data.SqlDbType.Int);
        sc.Parameters["@Id"].Value = userId;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();

        int id = (int)dr["ID"];
        
        string first_name = (string)dr["First_name"];
        string last_name = (string)dr["Last_name"];

        string result = first_name.Trim() + " " + last_name.Trim(); 
 
        dr.Close();

        sc.Connection.Close();

        return result; 
    }

    public int get_users_count()
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "SELECT count(*) FROM user_data";

        sc.Connection = sq;
        sc.Connection.Open();

        int recnum = (int)sc.ExecuteScalar();

        sc.Connection.Close();

        return recnum;
    }

    public string[] get_all_logins()
    {
        string[] selected_logins = new string[get_users_count()];

        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT Login FROM user_data";

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();

        int i = 0;

        while (dr.Read())
        {
            selected_logins[i] = dr["Login"].ToString().Trim();
            i++;
        }


        dr.Close();

        sc.Connection.Close();
        return selected_logins;
    }

    public bool isUser(string username)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = " IF EXISTS (SELECT ID FROM user_data WHERE Login=@username) SELECT 1";

        sc.Parameters.Add("@username", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters["@username"].Value = username;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        
        if (dr.HasRows)
        {
            sc.Connection.Close();
            return true;
        }
        else
        {
            sc.Connection.Close();
            return false;
        }

    }
    
    #endregion

    #region IMAGE_DATA_MANIPULATION 

    public void insert_image(int CompID, byte[] image, string img_type)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "INSERT INTO images (Image, ImageType, CompId) VALUES (@Image,@ImageType,@CompId)";

        sc.Parameters.Add("@Image", System.Data.SqlDbType.Image);
        sc.Parameters.Add("@ImageType", System.Data.SqlDbType.NVarChar, 5);
        sc.Parameters.Add("@CompId", System.Data.SqlDbType.Int); 

        sc.Parameters["@Image"].Value = image;
        sc.Parameters["@ImageType"].Value = img_type;
        sc.Parameters["@CompId"].Value = CompID; 
            
        sc.Connection = sq;
        sc.Connection.Close(); 
        sc.Connection.Open();
        sc.ExecuteNonQuery();
        sc.Connection.Close();
    }

    public void insert_small_image(int CompID, byte[] image)
    {
        imageProcessor small_img = new imageProcessor(); 
        
        SqlCommand sc = new SqlCommand();
        
        sc.CommandText = "UPDATE main_data SET smallLogo=@Image WHERE ID=@CompId"; 

        sc.Parameters.Add("@Image", System.Data.SqlDbType.Image);
        sc.Parameters.Add("@CompId", System.Data.SqlDbType.Int);

        sc.Parameters["@Image"].Value = small_img.create_thumbnail(image);  
        sc.Parameters["@CompId"].Value = CompID;

        sc.Connection = sq;
        sc.Connection.Open();
        sc.ExecuteNonQuery();
        sc.Connection.Close();

    }

    public byte[] select_image(int CompID)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT Image FROM images WHERE CompID=@CompID";
        sc.Parameters.Add("@CompID", System.Data.SqlDbType.Int);
        sc.Parameters["@CompID"].Value = CompID;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();

        byte[] img = (byte[])dr["Image"];

        return img;
    }

    public byte[] select_small_image(int CompID)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT smallLogo FROM main_data WHERE ID=@CompID";
        sc.Parameters.Add("@CompID", System.Data.SqlDbType.Int);
        sc.Parameters["@CompID"].Value = CompID;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();

        if (dr["smallLogo"] == DBNull.Value)
        {
            return null;
        }
        else
        {
            byte[] img = (byte[])dr["smallLogo"];
            return img;
        }
    }
  
    public string select_image_ext(int CompID)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT ImageType FROM images WHERE CompID=@CompID";
        sc.Parameters.Add("@CompID", System.Data.SqlDbType.Int);
        sc.Parameters["@CompID"].Value = CompID;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
        dr.Read();

        string image_type = (string)dr["ImageType"];

        dr.Close();
        sc.Connection.Close(); 

        return image_type;
    }

    public void update_image(int CompID, byte[] image, string img_type)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "UPDATE images SET Image=@Image, ImageType=@ImageType WHERE CompId=@CompId";

        sc.Parameters.Add("@Image", System.Data.SqlDbType.Image);
        sc.Parameters.Add("@ImageType", System.Data.SqlDbType.NVarChar, 5);
        sc.Parameters.Add("@CompId", System.Data.SqlDbType.Int);

        sc.Parameters["@Image"].Value = image;
        sc.Parameters["@ImageType"].Value = img_type;
        sc.Parameters["@CompId"].Value = CompID;

        sc.Connection = sq;
        sc.Connection.Close();
        sc.Connection.Open();
        
        sc.ExecuteNonQuery();

        sc.Connection.Close();
    }

    public void delete_image(int ID)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "DELETE FROM images WHERE CompID=@ID";
        sc.Parameters.Add("@ID", System.Data.SqlDbType.Int);
        sc.Parameters["@ID"].Value = ID;
        sc.Connection = sq;
        sc.Connection.Open(); 
        sc.ExecuteNonQuery();
        sc.Connection.Close();
    }
    
    #endregion

    #region SEARCH

    public SqlDataReader search_in_keywords(string key)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = "SELECT * FROM main_data as md INNER JOIN activity_data as ad ON md.ID=ad.CompId WHERE CONTAINS(Keywords, @search_key)";

        sc.Parameters.Add("@search_key", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters["@search_key"].Value = key;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();

        return dr;        
    } 

    #endregion

    #region ADMIN

    public string[] select_cities(int count, string prefix)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;
        
        SearchProcessor sp = new SearchProcessor();
        List<string> cities_lst = new List<string>();

        string ready_cond = "'" + prefix + "%'";
        
        sc.CommandText="SELECT TOP 10 Name FROM dict_cities WHERE Name LIKE "+ready_cond;

        // sc.Parameters.Add("@count");
        // sc.Parameters.Add("@Prefix", System.Data.SqlDbType.NVarChar, 60);
        // sc.Parameters["@count"].Value = count;
        // sc.Parameters["@Prefix"].Value = ready_cond;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();        

        while (dr.Read())
        {
            cities_lst.Add(sp.highlight_letters(dr["Name"].ToString(), prefix)); 
        }

        sc.Connection.Close();

        return cities_lst.ToArray(); 
    }

    public void insert_city(string city_name, string cily_region)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "INSERT INTO dict_cities (Name, Region) VALUES (@City, @Region)";
        
        sc.Parameters.Add("@City", System.Data.SqlDbType.NVarChar);
        sc.Parameters.Add("@Region", System.Data.SqlDbType.NVarChar);
                
        sc.Parameters["@City"].Value = city_name;
        sc.Parameters["@Region"].Value = cily_region;

        sc.Connection = sq;
        sc.Connection.Open();
        sc.ExecuteNonQuery();

        sc.Connection.Close();
        
    }

    public bool isCity(string word)
    {
        SqlCommand sc = new SqlCommand();
        SqlDataReader dr;

        sc.CommandText = " IF EXISTS (SELECT ID FROM dict_cities WHERE Name=@Word) SELECT 1";

        sc.Parameters.Add("@Word", System.Data.SqlDbType.NVarChar, 256);
        sc.Parameters["@Word"].Value = word;

        sc.Connection = sq;
        sc.Connection.Open();

        dr = sc.ExecuteReader();
               

        if (dr.HasRows)
        {
            sc.Connection.Close(); 
            return true;
        }
        else
        {
            sc.Connection.Close(); 
            return false;
        }
    }

    public void insert_log_event(string module, string message)
    {
        SqlCommand sc = new SqlCommand();

        sc.CommandText = "INSERT INTO event_log (Module, Message, DateTime) VALUES (@Module, @Message)";

        sc.Parameters.Add("@Module", System.Data.SqlDbType.NVarChar, 50);
        sc.Parameters.Add("@Message", System.Data.SqlDbType.Text);
        
        sc.Parameters["@Module"].Value=module;
        sc.Parameters["@Message"].Value = message;

        sc.Connection = sq;
        sc.Connection.Open();
        sc.ExecuteNonQuery();

        sc.Connection.Close();
    }


    #endregion

}